import mysql.connector
from mysql.connector import Error

def update_database():
    connection = None
    try:
        # 连接到MySQL服务器和数据库
        connection = mysql.connector.connect(
            host='ip',
            port=port,
            user='root',
            password='password',
            database='erp_db'
        )
        
        if connection.is_connected():
            cursor = connection.cursor()
            
            # 创建员工表
            create_employee_table = """
            CREATE TABLE IF NOT EXISTS employee (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                employee_no VARCHAR(50) NOT NULL COMMENT '员工编号',
                name VARCHAR(50) NOT NULL COMMENT '姓名',
                gender ENUM('MALE','FEMALE') COMMENT '性别',
                birth_date DATE COMMENT '出生日期',
                hire_date DATE NOT NULL COMMENT '入职日期',
                department VARCHAR(50) COMMENT '部门',
                position VARCHAR(50) COMMENT '职位',
                phone VARCHAR(20) COMMENT '联系电话',
                email VARCHAR(100) COMMENT '邮箱',
                address VARCHAR(200) COMMENT '地址',
                id_number VARCHAR(18) COMMENT '身份证号',
                bank_name VARCHAR(100) COMMENT '开户银行',
                bank_account VARCHAR(50) COMMENT '银行账户',
                status ENUM('ACTIVE','INACTIVE','LEFT') DEFAULT 'ACTIVE' COMMENT '状态',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                is_deleted TINYINT(1) DEFAULT 0,
                UNIQUE KEY uk_employee_no (employee_no),
                UNIQUE KEY uk_id_number (id_number)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='员工表'
            """
            cursor.execute(create_employee_table)
            print("员工表创建成功")
            
            # 创建工资表
            create_payroll_table = """
            CREATE TABLE IF NOT EXISTS payroll (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                employee_id BIGINT NOT NULL COMMENT '员工ID',
                pay_period VARCHAR(7) NOT NULL COMMENT '薪资期间 (YYYY-MM)',
                basic_salary DECIMAL(15,2) COMMENT '基本工资',
                allowance DECIMAL(15,2) DEFAULT 0 COMMENT '津贴',
                bonus DECIMAL(15,2) DEFAULT 0 COMMENT '奖金',
                overtime_pay DECIMAL(15,2) DEFAULT 0 COMMENT '加班费',
                deduction DECIMAL(15,2) DEFAULT 0 COMMENT '扣除',
                tax DECIMAL(15,2) DEFAULT 0 COMMENT '个人所得税',
                total_pay DECIMAL(15,2) COMMENT '应发工资',
                actual_pay DECIMAL(15,2) COMMENT '实发工资',
                status ENUM('DRAFT','APPROVED','PAID') DEFAULT 'DRAFT' COMMENT '状态',
                created_by BIGINT NOT NULL COMMENT '创建人',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                is_deleted TINYINT(1) DEFAULT 0,
                KEY idx_employee_id (employee_id),
                KEY idx_pay_period (pay_period),
                KEY idx_status (status),
                CONSTRAINT fk_payroll_employee FOREIGN KEY (employee_id) REFERENCES employee (id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='工资表'
            """
            cursor.execute(create_payroll_table)
            print("工资表创建成功")
            
            # 创建考勤表
            create_attendance_table = """
            CREATE TABLE IF NOT EXISTS attendance (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                employee_id BIGINT NOT NULL COMMENT '员工ID',
                work_date DATE NOT NULL COMMENT '工作日期',
                check_in_time TIME COMMENT '上班时间',
                check_out_time TIME COMMENT '下班时间',
                work_hours DECIMAL(5,2) COMMENT '工作小时数',
                overtime_hours DECIMAL(5,2) DEFAULT 0 COMMENT '加班小时数',
                status ENUM('PRESENT','ABSENT','LEAVE') DEFAULT 'PRESENT' COMMENT '状态',
                remark VARCHAR(200) COMMENT '备注',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                is_deleted TINYINT(1) DEFAULT 0,
                KEY idx_employee_id (employee_id),
                KEY idx_work_date (work_date),
                KEY idx_status (status),
                CONSTRAINT fk_attendance_employee FOREIGN KEY (employee_id) REFERENCES employee (id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考勤表'
            """
            cursor.execute(create_attendance_table)
            print("考勤表创建成功")
            
    except Error as e:
        print(f"更新数据库时出错: {e}")
        
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL连接已关闭")

if __name__ == "__main__":
    update_database()